﻿IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_CYXJ]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_CYXJ]
GO

/**********
[版本号]1.0.0
[创建时间]2018-12-15
[作者]SJ
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--出院小结GXBZ_EMR_CYXJ数据上传
[返回值]
[调用的USP]
	[调用实例] EXEC USP_GXBZ_EMR_CYXJ
[修改历史]
**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_CYXJ]    
AS 
  SET XACT_ABORT ON 
DECLARE 
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN

	
	
	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_CYXJ')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_CYXJ','2015-07-01')
	END 
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_CYXJ'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM ZYCYXJBG(NOLOCK)
	
	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN

	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_CYXJ(
		ZYLSH		varchar(64)          not null,
		XGBZ		varchar(1)           not null,
		GRJBXXBSH	varchar(64)          not null,
		CXJMJKDABH	varchar(17)          not null,
		JKKH		varchar(64)          null,
		YLJGDM		varchar(22)          null,
		YLJGMC		varchar(70)          null,
		CJSJ		datetime             null,
		WDCJZGH		varchar(64)          null,
		WDCJZXM		varchar(30)          null,
		WDGLJGDM	varchar(22)          null,
		WDGLJGMC	varchar(70)          null,
		ZYYSQMSJ	datetime             null,
		ZYYSGH		varchar(64)          null,
		ZYYSXM		varchar(30)          null,
		SJYSQMSJ	datetime             null,
		SJYSGH		varchar(64)          null,
		SJYSXM		varchar(30)          null,
		NXRDH		varchar(24)          null,
		NXRXM		varchar(30)          null,
		RYRQSJ		datetime             null,
		CYRQSJ		datetime             null,
		BCH			varchar(20)          null,
		BCHMC		varchar(50)          null,
		BFH			varchar(20)          null,
		BFHMC		varchar(50)          null,
		KSBM		varchar(20)          null,
		KSMC		varchar(50)          null,
		BQBM		varchar(20)          null,
		BQMC		varchar(50)          null,
		RYZD		varchar(1024)        null,
		RXYZDDM		varchar(64)          null,
		RZYBMDM		varchar(64)          null,
		RZYZHDM		varchar(64)          null,
		CXYZDDM		varchar(64)          null,
		CZYBMDM		varchar(64)          null,
		CZYZHDM		varchar(64)          null,
		ZYSZGCJG	varchar(1024)        null,
		CYZD		varchar(1024)        null,
		CYQK		varchar(1024)         null,
		SSJCZBM		varchar(50)          null,
		SSKSSJ		datetime             null,
		SSQKLBDM	varchar(2)           null,
		YHLBDM		varchar(4)           null,
		YHLBMC		varchar(100)         null,
		MZFFDM		varchar(20)          null,
		SSGCMS		varchar(1024)        null,
		ZZZF		varchar(100)          null,
		ZLGCMS		varchar(1024)        null,
		ZLJGDM		varchar(20)          null,
		SJZYTS		decimal(5)           null,
		ZYJZFF		varchar(100)         null,
		ZYYYFF		varchar(100)         null,
		CYYZ		varchar(1024)        null,
		YXFZJCJG	varchar(1024)        null,
		MJ			varchar(16)          not null,
		TBRQ		datetime             not null,
		SCZT		varchar(1)           not null,
		CS_JYBZ		int                  not null,
		SCSJ		datetime             not null
	) ON [PRIMARY]
	
	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_CYXJ (
		ZYLSH,
		XGBZ,
		GRJBXXBSH,
		CXJMJKDABH,
		JKKH,
		YLJGDM,
		YLJGMC,
		CJSJ,
		WDCJZGH,
		WDCJZXM,
		WDGLJGDM,
		WDGLJGMC,
		ZYYSQMSJ,
		ZYYSGH,
		ZYYSXM,
		SJYSQMSJ,
		SJYSGH,
		SJYSXM,
		NXRDH,
		NXRXM,
		RYRQSJ,
		CYRQSJ,
		BCH,
		BCHMC,
		BFH,
		BFHMC,
		KSBM,
		KSMC,
		BQBM,
		BQMC,
		RYZD,
		RXYZDDM,
		RZYBMDM,
		RZYZHDM,
		CXYZDDM,
		CZYBMDM,
		CZYZHDM,
		ZYSZGCJG,
		CYZD,
		CYQK,
		SSJCZBM,
		SSKSSJ,
		SSQKLBDM,
		YHLBDM,
		YHLBMC,
		MZFFDM,
		SSGCMS,
		ZZZF,
		ZLGCMS,
		ZLJGDM,
		SJZYTS,
		ZYJZFF,
		ZYYYFF,
		CYYZ,
		YXFZJCJG,
		MJ,
		TBRQ,
		SCZT,
		CS_JYBZ,
		SCSJ
				)
	SELECT
		A.JZLSH  ZYLSH,
		A.XGBZ XGBZ,
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS GRJBXXBSH,		--个人基本信息标识号
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS CXJMJKDABH,		--城乡居民健康档案编号
		A.KH JKKH,
		isnull(C.SPTDM,'500103000000001')  YLJGDM,
		isnull(C.JGMC,'渝中区卫计委') YLJGMC,
		A.DJRQ CJSJ,
		A.CZYGH WDCJZGH,
		A.CZYXM WDCJZXM,
		'500103000000001' WDGLJGDM,
		'渝中区卫计委' WDGLJGMC,
		NULL ZYYSQMSJ,
		A.ZYYSGH ZYYSGH,
		A.ZYYSXM ZYYSXM,
		A.QMRQSJ SJYSQMSJ,
		A.SJYSGH SJYSGH,
		A.SJYSXM SJYSXM,
		B.LXRDH NXRDH,
		B.LXRXM NXRXM,
		A.RYSJ RYRQSJ,
		A.CYSJ CYRQSJ,
		A.CH BCH,
		A.CH+'病床' BCHMC,
		'' BFH,
		'' BFHMC,
		A.KSDM KSBM,
		A.KSMC KSMC,
		'' BQBM,
		'' BQMC,
		A.RYZD RYZD,
		A.RXYZDDM RXYZDDM,
		A.RZYBMDM RZYBMDM,
		A.RZYZHDM RZYZHDM,
		A.CXYZDDM CXYZDDM,
		A.CZYBMDM CZYBMDM,
		A.CZYZHDM CZYZHDM,
		'' ZYSZGCJG,
		A.CYZD CYZD,
		A.CYQK CYQK,
		D.SSCZBM SSJCZBM,
		D.SSKSSJ SSKSSJ,
		D.SSQKLBDM SSQKLBDM,
		D.QKYHDJ YHLBDM,
		case D.QKYHDJ
			when '11' then 'I/甲'
			when '12' then 'I/乙'
			when '13' then 'I/丙'
			when '21' then 'II/甲'
			when '22' then 'II/乙'
			when '23' then 'II/丙'
			when '31' then 'III/甲'
			when '32' then 'III/乙'
			when '33' then 'III/丙'
			else '无切口或其他'
		end YHLBMC,
		D.MZFS MZFFDM,
		D.SSGCMS SSGCMS,
		'' ZZZF,
		A.ZLGC ZLGCMS,
		'' ZLJGDM,
		convert(decimal(5,0),A.ZYTS)  SJZYTS,
		'' ZYJZFF,
		'' ZYYYFF,
		A.CYYZ CYYZ,
		'' YXFZJCJG,
		A.MJ MJ,
		A.TBRQ TBRQ,
		'0' AS SCZT,
		0 AS CS_JYBZ,
		GETDATE() AS SCSJ
	FROM ZYCYXJBG A(NOLOCK)
	LEFT JOIN JBBRJBXXB B(NOLOCK) ON A.ZJHM=B.ZJHM  AND A.YLJGDM=B.YLJGDM
	LEFT JOIN SYS_ZCJG_DZ C(NOLOCK) ON A.YLJGDM=C.QPTDM
	LEFT JOIN ZYSSJLBG D ON A.JZLSH = D.JZLSH and A.YLJGDM=D.YLJGDM
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01') 
	and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) is not NULL
	
	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_CYXJ A,#TEMP_GXBZ_EMR_CYXJ B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.YLJGDM=B.YLJGDM AND A.ZYLSH=B.ZYLSH
	
	
	--插入数据至中间库
	INSERT INTO GXBZ_EMR_CYXJ (ZYLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,JKKH,YLJGDM,YLJGMC,
		CJSJ,WDCJZGH,WDCJZXM,WDGLJGDM,WDGLJGMC,ZYYSQMSJ,ZYYSGH,ZYYSXM,SJYSQMSJ,SJYSGH,
		SJYSXM,NXRDH,NXRXM,RYRQSJ,CYRQSJ,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,BQBM,BQMC,RYZD,
		RXYZDDM,RZYBMDM,RZYZHDM,CXYZDDM,CZYBMDM,CZYZHDM,ZYSZGCJG,CYZD,CYQK,SSJCZBM,SSKSSJ,
		SSQKLBDM,YHLBDM,YHLBMC,MZFFDM,SSGCMS,ZZZF,ZLGCMS,ZLJGDM,SJZYTS,ZYJZFF,ZYYYFF,CYYZ,
		YXFZJCJG,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ)
	SELECT ZYLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,JKKH,YLJGDM,YLJGMC,
		CJSJ,WDCJZGH,WDCJZXM,WDGLJGDM,WDGLJGMC,ZYYSQMSJ,ZYYSGH,ZYYSXM,SJYSQMSJ,SJYSGH,
		SJYSXM,NXRDH,NXRXM,RYRQSJ,CYRQSJ,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,BQBM,BQMC,RYZD,
		RXYZDDM,RZYBMDM,RZYZHDM,CXYZDDM,CZYBMDM,CZYZHDM,ZYSZGCJG,CYZD,CYQK,SSJCZBM,SSKSSJ,
		SSQKLBDM,YHLBDM,YHLBMC,MZFFDM,SSGCMS,ZZZF,ZLGCMS,ZLJGDM,SJZYTS,ZYJZFF,ZYYYFF,CYYZ,
		YXFZJCJG,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ
	FROM #TEMP_GXBZ_EMR_CYXJ
	
	COMMIT TRAN
	
		SELECT 'T' AS SRESULT, '表：GXBZ_EMR_CYXJ数据抽取成功！' AS SHINT
			
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
		VALUES('表：GXBZ_EMR_ZYBASY数据抽取成功','GXBZ_EMR_CYXJ',GETDATE(),'T','USP_GXBZ_EMR_CYXJ') 
		
		--更新上传时间
		UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_CYXJ'
		
	END TRY BEGIN CATCH
			SELECT 'F' AS SRESULT, '表：GXBZ_EMR_CYXJ数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
			ROLLBACK TRAN
			
		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_CYXJ数据抽取失败','GXBZ_EMR_CYXJ',GETDATE(),'F','USP_GXBZ_EMR_CYXJ',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END 